﻿
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using ICSharpCode.TextEditor;
using ICSharpCode.TextEditor.Document;

namespace Pub
{
    public class CGridViewMenu
    {

        public ADOTest ado2;
        public string sTableName = "";
        public string strSQL_Def = "";
        public ToolStripMenuItem CMadd = new ToolStripMenuItem("添加");
        public ToolStripMenuItem CMcancle = new ToolStripMenuItem("撤销");
        public ToolStripMenuItem CMdel = new ToolStripMenuItem("删除");
        public ToolStripMenuItem CMedt = new ToolStripMenuItem("修改");
        public ToolStripMenuItem CMref = new ToolStripMenuItem("刷新");
        public ToolStripMenuItem CMsave = new ToolStripMenuItem("保存");
        public ToolStripMenuItem CMScript = new ToolStripMenuItem("导出脚本");

        public ToolStripMenuItem CMS_Insert = new ToolStripMenuItem("Insert语句");
        public ToolStripMenuItem CMS_Delete = new ToolStripMenuItem("Delete语句");
        public ToolStripMenuItem CMS_Creat = new ToolStripMenuItem("Creat语句");

        public ToolStripMenuItem CMS_SQL = new ToolStripMenuItem("查询窗口");
        public ToolStripMenuItem CMS_File = new ToolStripMenuItem("文件...");
        public ToolStripMenuItem CMS_Copy = new ToolStripMenuItem("剪贴板");

        public DataGridView dataGridView1;
        public TextEditorControl tboxSQL;
        public CTextEditMenu tboxSQl_Menu;
        public int I_Sta = 0;
        public ContextMenuStrip gridVMenu = new ContextMenuStrip();

        string[] sM1 = new string[]{"CREATE到","ALTER到","DROP到","DROP和CREATE到","-",
            "SELECT到","INSERT到","UPDATE到","DELETE到","-","EXECUTE到"};
        string[] sM2 = new string[] { "查询编辑窗口", "文件...", "剪贴板" };

        public CGridViewMenu()
        {
            init();
        }

        public CGridViewMenu(DataGridView gridview, ADOTest ado1)
        {
            dataGridView1 = gridview;
            ado2 = ado1;
            dataGridView1.ContextMenuStrip = gridVMenu;
            init();
        }

        public CGridViewMenu(DataGridView gridview, ADOTest ado1, CTextEditMenu sqlMenu)
        {
            dataGridView1 = gridview;
            ado2 = ado1;
            dataGridView1.ContextMenuStrip = gridVMenu;
            tboxSQl_Menu = sqlMenu;
            init();
        }

        public void init()
        {
            gridVMenu.Items.Add(CMadd);
            gridVMenu.Items.Add(CMedt);
            gridVMenu.Items.Add(CMdel);
            gridVMenu.Items.Add(CMcancle);
            gridVMenu.Items.Add(CMsave);
            gridVMenu.Items.Add(CMref);
            gridVMenu.Items.Add(CMScript);

            //CMScript.DropDownItems.Add(CMS_Insert);
            //CMScript.DropDownItems.Add(CMS_Delete);
            //CMScript.DropDownItems.Add(CMS_Creat);

            for (int i = 0; i < sM1.Length;i++ )
            {
                if (sM1[i] != "-")
                {
                    ToolStripMenuItem item1 = new ToolStripMenuItem(sM1[i]);
                    CMScript.DropDownItems.Add(item1);

                    for (int j = 0; j < sM2.Length; j++)
                    {
                        ToolStripMenuItem item2 = new ToolStripMenuItem(sM2[j]);
                        item2.Tag = i * 100 + j;
                        item2.Click += new EventHandler(Script_Click);
                        item1.DropDownItems.Add(item2);
                    }
                } 
                else
                {
                    ToolStripSeparator item1 = new ToolStripSeparator();
                    CMScript.DropDownItems.Add(item1);
                }

            }

            CMadd.Click += new EventHandler(CMadd_Click);
            CMedt.Click += new EventHandler(CMedt_Click);
            CMdel.Click += new EventHandler(CMdel_Click);
            CMcancle.Click += new EventHandler(CMcancle_Click);
            CMsave.Click += new EventHandler(CMsave_Click);
            CMref.Click += new EventHandler(CMref_Click);

            gridVMenu.Opened += new EventHandler(contextMenuStrip1_Opened);
        }

        //右键菜单 添加
        private void CMadd_Click(object sender, EventArgs e)
        {
            if (CMadd.Enabled == true)
            {
                I_Sta = 1;
                if (dataGridView1.ColumnCount > 0)
                {
                    int i = ado2.ds_count;
                    DataRow dr = ado2.ds.Tables[0].NewRow();
                    ado2.ds.Tables[0].Rows.Add(dr);
                }
            }
        }

        //右键菜单 撤销
        private void CMcancle_Click(object sender, EventArgs e)
        {
            if (CMcancle.Enabled == true)
            {

            }
        }

        //右键菜单 删除
        private void CMdel_Click(object sender, EventArgs e)
        {
            if (CMdel.Enabled == true)
            {
                I_Sta = 3;
                if (CPub.MsgBox("确定要写入到数据库吗？", "删除"))
                {
                    string strSQL = "";
                    if (dataGridView1.SelectedRows.Count > 0)
                    {
                        int r_Count = dataGridView1.SelectedRows.Count;
                        strSQL = "DELETE FROM " + sTableName + " WHERE " + dataGridView1.Columns[0].Name + " IN (";
                        for (int i = 0; i < r_Count; i++)
                        {
                            //ado2.ds.Tables[0].Rows[dataGridView1.SelectedRows[0].Index].Delete();
                            if (i != 0) strSQL += ",";
                            strSQL += dataGridView1.SelectedRows[i].Cells[0].Value.ToString();
                        }
                        strSQL += ")";
                    }
                    //ado2.update(CADO.I_DEL);
                    if (CPub.MsgBox(strSQL, "删除"))
                    {
                        ado2.cmd1(strSQL);
                    }
                }
            }
        }

        //右键菜单 编辑
        private void CMedt_Click(object sender, EventArgs e)
        {
            if (CMedt.Enabled == true)
            {
                I_Sta = 2;

            }
        }

        //右键菜单 刷新
        private void CMref_Click(object sender, EventArgs e)
        {
            dataGridView1.DataSource = ado2.ds.Tables[0];
        }

        //右键菜单 保存
        private void CMsave_Click(object sender, EventArgs e)
        {
            if (CMsave.Enabled == true)
            {
                if (CPub.MsgBox("确定要写入到数据库吗？", "保存"))
                {
                    switch (I_Sta)
                    {
                        case 1: //add
                        case 2: //edit
                            if (dataGridView1.Rows.Count > 0)
                            {
                                for (int i = 0; i < ado2.ds.Tables[0].Rows.Count; i++)
                                {
                                    for (int j = 0; j < ado2.ds.Tables[0].Columns.Count; j++)
                                    {
                                        ado2.ds.Tables[0].Rows[i][j] = dataGridView1.Rows[i].Cells[j].Value;
                                    }
                                }
                            }
                            break;
                    }
                    ado2.update(CADO.I_EDT);
                }
            }
        }

        //右键菜单 按钮可见
        private void contextMenuStrip1_Opened(object sender, EventArgs e)
        {
            if (dataGridView1.ColumnCount > 0)
            {
                CMadd.Enabled = true;
                CMedt.Enabled = true;
                CMdel.Enabled = true;
                CMsave.Enabled = true;
            }
            else
            {
                CMadd.Enabled = false;
                CMedt.Enabled = false;
                CMdel.Enabled = false;
                CMsave.Enabled = false;
            }
        }

        //导出Insert脚本
        private string getInsert()
        {
            StringBuilder sTxt = new StringBuilder();
            StringBuilder sTxt_Col = new StringBuilder();
            StringBuilder sTxt_Val = new StringBuilder();

            for (int i = 0; i < dataGridView1.ColumnCount; i++)
            {
                if (i != 0) sTxt_Col.Append(",");
                sTxt_Col.Append("[" + dataGridView1.Columns[i].Name + "]");
            }

            for (int i = 0; i < dataGridView1.RowCount - 1; i++)
            {
                if (dataGridView1.Rows[i].Cells[0].Value != null)
                {
                    for (int j = 0; j < dataGridView1.ColumnCount; j++)
                    {
                        if (j != 0) sTxt_Val.Append(",");
                        string sVal = dataGridView1.Rows[i].Cells[j].Value.ToString().Trim();

                        Type typ1 = dataGridView1.Columns[j].ValueType;
                        if (typ1 == typeof(System.Decimal)
                            || typ1 == typeof(Int16) || typ1 == typeof(Int32) || typ1 == typeof(Int64)
                            || typ1 == typeof(float) || typ1 == typeof(Double) || typ1 == typeof(byte)
                            || typ1 == typeof(Boolean))
                        {
                            if (sVal == "") sVal = "0";
                        }
                        else if (typ1 == typeof(String) || typ1 == typeof(DateTime))
                        {
                            sVal = "'" + sVal.Replace("'", "''") + "'";
                        }
                        else
                        {
                            CDebug.Send(typ1.ToString());
                            sVal = "'" + sVal.Replace("'", "''") + "'";
                        }
                        sTxt_Val.Append(sVal);
                    }
                }

                if (i != 0) sTxt.Append(" \n");
                sTxt.Append("INSERT INTO " + sTableName + "(" + sTxt_Col + ") VALUES(" + sTxt_Val + ");");
                sTxt_Val.Remove(0, sTxt_Val.Length);
            }

            return sTxt.ToString();
        }

        private string getCreate()
        {
            string sTxt = "";
            string sKey = "";
            string strSQL = "SELECT \n"
                + "(case when a.colorder=1 then d.name else '' end) as 表名, \n"
                + "  a.colorder as 字段序号, \n"
                + "  a.name as 字段名, \n"
                + "  (case when COLUMNPROPERTY( a.id,a.name, 'IsIdentity' )=1 then '√' else '' end) as 标识,  \n"
                + "  (case when (SELECT count(*) FROM sysobjects \n"
                + "    WHERE (name in \n"
                + "      (SELECT name FROM sysindexes \n"
                + "      WHERE (id = a.id)   AND (indid in \n"
                + "        (SELECT indid FROM sysindexkeys \n"
                + "          WHERE (id = a.id) AND (colid in \n"
                + "            (SELECT colid FROM syscolumns \n"
                + "            WHERE (id = a.id) AND (name = a.name)) \n"
                + "    ))))) \n"
                + "  AND (xtype = 'PK' ))>0 then '√' else '' end) as 主键, \n"
                + "b.name as 类型, \n"
                + "a.length as 占用字节数,  \n"
                + "COLUMNPROPERTY(a.id,a.name,'PRECISION' ) as    长度,  \n"
                + "isnull(COLUMNPROPERTY(a.id,a.name,'Scale' ),0) as 小数位数, \n"
                + "(case when a.isnullable=1 then '√' else '' end) as 允许空, \n"
                + "isnull(e.text,'' ) as 默认值, \n"
                + "isnull(g.[value],'' ) AS 字段说明 \n"
                + "FROM syscolumns a left join systypes b  \n"
                + "on a.xtype=b.xusertype \n"
                + "inner join sysobjects d \n"
                + "on a.id=d.id and d.xtype='U' and d.name<> 'dtproperties'  \n"
                + "left join syscomments e \n"
                + "on a.cdefault=e.id \n"
                + "left join sys.extended_properties g \n"
                + "on a.id=g.major_id AND a.colid = g.minor_id  \n"
                + "  where d.name='" + sTableName + "' \n"
                + "order by a.id,a.colorder \n";

            CDebug.Send(strSQL);
            DataSet ds1 = ado2.sel1(strSQL);
            if(!CDB.dsIsNull(ds1))
            {
                sTxt = "\n"
                    + "CREATE TABLE [" + sTableName + "]( \n";
                for(int i=0;i<ds1.Tables[0].Rows.Count;i++)
                {
                    if (i != 0) sTxt += ",\n";
                    DataRow dr1 = ds1.Tables[0].Rows[i];
                    string sfield = dr1["字段名"].ToString();
                    string stype = dr1["类型"].ToString();
                    string slen = dr1["长度"].ToString();
                    string snull = dr1["允许空"].ToString();
                    string spk = dr1["主键"].ToString();

                    sTxt += "    [" + sfield + "] [" + stype + "] "
                        + ("bigint,float,int,datetime".IndexOf(stype) == -1 ? "(" + slen + ") " : "")
                        + (string.IsNullOrEmpty(snull) ? "NOT NULL" : "NULL") + " ";

                    if(!string.IsNullOrEmpty(spk))
                    {
                        if (sKey != "") sKey += ", \n";
                        sKey += "    [" + sfield + "] ASC";
                    }
                }

                //创建主键
                if (sKey != "")
                {
                    sTxt += ", \n"
                        + "  CONSTRAINT [PK_" + sTableName + "] PRIMARY KEY CLUSTERED ( \n"
                        + sKey + "\n"
                        + "  )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]";
                }
                sTxt += ") \n";
            }
            return sTxt;
        }

        private string getDrop()
        {
            string sTxt = "\n"
                + "DROP TABLE [" + sTableName + "] \n";
            return sTxt;
        }

        /// <summary>
        /// 保存脚本到文件
        /// </summary>
        /// <param name="sTxt"></param>
        private void SaveScript(string sTxt)
        {
            SaveFileDialog dialog = new SaveFileDialog();
            dialog.Filter = "*.sql|*.sql|*.*|*.*";
            if (dialog.ShowDialog() == DialogResult.OK)
            {
                string sFileName = dialog.FileName;
                CFile.Write(sFileName, sTxt, Encoding.Default, true);
            }
        }

        private void SaveScript(string sTxt, int iTyp)
        {
            switch (iTyp)
            {
                case 0:
                    tboxSQl_Menu.AppendText(sTxt);
                    break;
                case 1:
                    SaveScript(sTxt);
                    break;
                case 2:
                    Clipboard.SetText(sTxt);
                    break;
            }
        }

        private void Script_Click(object sender, EventArgs e)
        {
            ToolStripMenuItem item1 = (ToolStripMenuItem)sender;
            int iTag = Convert.ToInt32(item1.Tag);
            CDebug.Send( item1.Text + iTag);
            string sTxt = "";

            switch (iTag / 100)
            {
                case 0: //create
                    sTxt = getCreate();
                    SaveScript(sTxt, iTag % 100);
                    break;
                case 1: //alter
                    
                    break;
                case 2: //drop
                    sTxt = getDrop();
                    SaveScript(sTxt, iTag % 100);
                    break;
                case 3: //drop and create

                    break;
                case 4: //-
                    break;
                case 5: //select

                    break;
                case 6: //insert
                    sTxt = getInsert();
                    SaveScript(sTxt, iTag % 100);
                    break;
            }

        }



    }
}